package com.kcs.fxmargin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import com.kcs.fxmargin.bo.SetupRoleProfileBO;
import com.kcs.fxmargin.model.MRole;
import com.kcs.fxmargin.model.MUsersAuth;

public class SetupRoleProfileDAO {
	
	public SetupRoleProfileBO getFunctionList(SetupRoleProfileBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<SetupRoleProfileBO> resultList = null;
		SetupRoleProfileBO srp = null;
		
		try{
			sql = "select m.id menu_id"
			     +" , m.name menu_name"
				 +" , a.id auth_id"
			     +" , a.flag auth_flag"
				 +" , a.role_id, r.code"
				 +" from m_menu m "
				 +" left outer join m_users_auth a on m.id=a.menu_id and a.active_status='A'"
				 +" left outer join m_role r on a.role_id=r.id and r.active_status='A'"
				 +" where m.active_status='A'"
				 +" and m.parent_menu is not null";
			
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<SetupRoleProfileBO>();
				
				while(rs.next()){
					srp = new SetupRoleProfileBO();
					srp.setMenuId(rs.getString("menu_id"));
					srp.setMenuName(rs.getString("menu_name"));
					srp.setAuthId(rs.getString("auth_id"));
					srp.setAuthFlag(rs.getString("auth_flag"));
					srp.setRoleId(rs.getString("role_id"));
					srp.setCode(rs.getString("code"));

					resultList.add(srp);
				}
				
				bo.setFunctionList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
	public void updateRoleProfile(SetupRoleProfileBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		SetupRoleProfileBO srp = null;
		String sql = null;
		
		try{
			
			for(int i=0; i<bo.getFunctionList().size(); i++){
				srp = bo.getFunctionList().get(i);
				
				if(srp.getAuthId() != null){
					sql = "update m_users_auth set flag = ?"
					     +" where id=?";
					
					pstmt = conn.prepareStatement(sql);
					pstmt.setString(1, srp.getAuthFlag());
					pstmt.setString(2, srp.getAuthId());
					pstmt.executeUpdate();
					
				}
			}
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}

		}
	}
	
	public boolean insertMUsersAuth(MUsersAuth model, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		String sql = null;
		boolean result = false;
		String userLogin = null;
		
		try{
			sql = "insert into m_users_auth "
				 +" (id "
				 +" ,menu_id "
				 +" ,role_id "
				 +" ,flag "
				 +" ,active_status "
				 +" ,create_by "
				 +" ,create_dt "
				 +" ,update_by "
				 +" ,update_dt) "
				 +" values(?,?,?,?,?,?,sysdatetime(),?,sysdatetime())";
			
			System.out.println(sql);
			System.out.println(">id:"+model.getId());
			System.out.println(">menuId:"+model.getMenuId());
			System.out.println(">roleId:"+model.getRoleId());
			System.out.println(">flag:"+model.getFlag());
			System.out.println(">activeStatus:"+model.getActiveStatus());
			
			
			userLogin = "SYSTEM";
			
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, model.getId());
			pstmt.setString(2, model.getMenuId());
			pstmt.setString(3, model.getRoleId());
			pstmt.setInt(4, model.getFlag());
			pstmt.setString(5, model.getActiveStatus());
			pstmt.setString(6, userLogin);
			pstmt.setString(7, userLogin);
			pstmt.execute();
			
			result = true;
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}

		}
		
		return result;
	}
	
	public SetupRoleProfileBO getRoleList(SetupRoleProfileBO bo, Connection conn)throws Exception{
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = null;
		List<MRole> resultList = null;
		MRole mr = null;
		
		try{
			sql = "select * from m_role";
			
			System.out.println(sql);
			
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			
			if(rs != null){
				resultList = new ArrayList<MRole>();
				
				while(rs.next()){
					mr = new MRole();
					mr.setId(rs.getString("id"));
					mr.setCode(rs.getString("code"));
					mr.setName(rs.getString("name"));
					mr.setDescription(rs.getString("description"));
					mr.setActiveStatus(rs.getString("active_status"));

					resultList.add(mr);
				}
				
				bo.setRoleList(resultList);
			}
			
			
		}catch(Exception e){
			throw e;
		}finally{
			if(pstmt != null){
				try{
					pstmt.close();
				}catch(Exception ex){
					throw ex;
				}
			}
			
			if(rs != null){
				try{
					rs.close();
				}catch(Exception ex){
					throw ex;
				}
			}
		}
		
		return bo;
	}
	
}
